

package cardval.dao;

import cardval.entity.CardInfo;
import cardval.util.StringUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/** 
* @ClassName: CardInfoDao 
* @Description: TODO
* @author Renne66  465119455@qq.com
* @date 2016年4月29日 上午10:21:05 
*  
*/
@Repository("cardInfoDao")
public class CardInfoDao {
	private static final Logger logger = Logger.getLogger(CardInfoDao.class);
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 添加卡信息
	 * @param paramsCard
	 * @return
	 */
	public int addCardInfo(CardInfo paramsCard){
		//新增流水号和商户号
		String sql = "INSERT INTO card_info (SERIALNO_,PARTNERNO_,CARD_NO_,CARD_NAME_,CARD_MOBILE_,CARD_IDCARD_,rspCd,rspDesc) VALUES (?,?,?,?,?,?,?,?)";
		List<Object> list = new ArrayList<>();
		list.add(paramsCard.getSERIALNO_());
		list.add(paramsCard.getPARTNERNO_());
		list.add(paramsCard.getCARD_NO_());
		list.add(paramsCard.getCARD_NAME_());
		list.add(paramsCard.getCARD_MOBILE_());
		list.add(paramsCard.getCARD_IDCARD_());
		list.add(paramsCard.getRspCd());
		list.add(paramsCard.getRspDesc());
		logger.info("-------------------------------addCardInfo:"+sql);
		logger.info("addCardInfo:"+list.toString());
		return jdbcTemplate.update(sql, list.toArray());
	}
	
	/**
	 * 查询卡信息
	 * @param paramsCard
	 * @param offset
	 *@param limit @return
	 *public List<CardInfo> queryCardInfo(CardInfo paramsCard, int offset, int limit)
	 */
	//更改查询显示的方式
/*	public List<CardInfo> queryCardInfo(CardInfo paramsCard, int offset, int limit){
		StringBuffer buffer = new StringBuffer("SELECT * FROM card_info WHERE CARD_NO_ = ? ");
		List<Object> list = new ArrayList<>();
		list.add(paramsCard.getCARD_NO_());
		//新增流水号和商户号字段
		if(!StringUtil.isEmpty(paramsCard.getSERIALNO_())){
			buffer.append(" AND SERIALNO_ = ?");
			list.add(paramsCard.getSERIALNO_());
		}
		if(!StringUtil.isEmpty(paramsCard.getPARTNERNO_())){
			buffer.append(" AND PARTNERNO_ = ?");
			list.add(paramsCard.getPARTNERNO_());
		}
		if(!StringUtil.isEmpty(paramsCard.getCARD_NAME_())){
			buffer.append(" AND CARD_NAME_ = ?");
			list.add(paramsCard.getCARD_NAME_());
		}
		if(!StringUtil.isEmpty(paramsCard.getCARD_MOBILE_())){
			buffer.append(" AND CARD_MOBILE_ = ?");
			list.add(paramsCard.getCARD_MOBILE_());
		}
		if(!StringUtil.isEmpty(paramsCard.getCARD_IDCARD_())){
			buffer.append(" AND CARD_IDCARD_ = ?");
			list.add(paramsCard.getCARD_IDCARD_());
		}
		
		logger.info("---------------------queryCardInfo:"+buffer.toString());
		logger.info("queryCardInfo:"+list.toString());
		buffer.append(" limit " + offset + ", " + limit);
		List<CardInfo> cardInfos = jdbcTemplate.query(buffer.toString(),list.toArray(), new BeanPropertyRowMapper<CardInfo>(CardInfo.class));
		return cardInfos;
	}*/
	//显示银行卡的信息全部显示
	public List<CardInfo> queryCardInfo(String serialNo,String partnerNo, String cardNo,String cardName, String cardMobile,String cardIdcard, int limit, int offset) {
        String sql = "SELECT * FROM card_info WHERE 1=1";
        //新增流水号查询
        if (StringUtil.isNotEmpty(serialNo)) {
            sql += " and SERIALNO_ = '" + serialNo + "'";
        }
        //新增商户号查询
        if (StringUtil.isNotEmpty(partnerNo)) {
            sql += " and PARTNERNO_ = '" + partnerNo + "'";
        }
        if (StringUtil.isNotEmpty(cardNo)) {
            sql += " and CARD_NO_ = '" + cardNo + "'";
        }
        //新增姓名和手机号查询
        if (StringUtil.isNotEmpty(cardName)) {
            sql += " and CARD_NAME_ = '" + cardName + "'";
        }
        if (StringUtil.isNotEmpty(cardMobile)) {
            sql += " and CARD_MOBILE_ = '" + cardMobile + "'";
        }
        
        
        if (StringUtil.isNotEmpty(cardIdcard)) {
            sql += " and CARD_IDCARD_ = '" + cardIdcard + "'";
        }
        sql += " limit " + offset + "," + limit;
        logger.info("queryCardInfos:" + sql);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CardInfo.class));
    }

	public long getCountCard(String serialNo,String partnerNo, String cardNo, String cardName, String cardMobile, String cardIdcard ) {
		String sql = "SELECT COUNT(1) FROM card_info WHERE 1=1";
		//新增流水号
		if (StringUtil.isNotEmpty(serialNo)) {
            sql += " and SERIALNO_ = '" + serialNo + "'";
        }
		 //新增商户号查询
        if (StringUtil.isNotEmpty(partnerNo)) {
            sql += " and PARTNERNO_ = '" + partnerNo + "'";
        }
		if (StringUtils.isNotBlank(cardNo)) {
			sql += " and CARD_NO_ = '" + cardNo + "'";
		}
		//新增姓名和手机号查询
        if (StringUtil.isNotEmpty(cardName)) {
            sql += " and CARD_NAME_ = '" + cardName + "'";
        }
        if (StringUtil.isNotEmpty(cardMobile)) {
            sql += " and CARD_MOBILE_ = '" + cardMobile + "'";
        }
		//新增商户号查询
        if (StringUtil.isNotEmpty(cardIdcard)) {
            sql += " and CARD_IDCARD_ = '" + cardIdcard + "'";
        }
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		return (long) list.get(0).get("count(1)");
	}

	/**
	 * 查询卡信息
	 * @param paramsCards
	 */
	public List<CardInfo> queryCardInfo(String serialNo,String partnerNo, String cardNo, String cardName, String cardMobile,String cardIdcard) {
		 String sql = "SELECT * FROM card_info where  1=1 ";
		//新增流水号
			if (StringUtil.isNotEmpty(serialNo)) {
	            sql += " and SERIALNO_ = '" + serialNo + "'";
	        }
			 //新增商户号查询
	        if (StringUtil.isNotEmpty(partnerNo)) {
	            sql += " and PARTNERNO_ = '" + partnerNo + "'";
	        }
	        if (StringUtil.isNotEmpty(cardNo)) {
	            sql += " and CARD_NO_ = '" + cardNo + "'";
	        }
	      //新增姓名和手机号查询
	        if (StringUtil.isNotEmpty(cardName)) {
	            sql += " and CARD_NAME_ = '" + cardName + "'";
	        }
	        if (StringUtil.isNotEmpty(cardMobile)) {
	            sql += " and CARD_MOBILE_ = '" + cardMobile + "'";
	        }
	      //新增商户号查询
	        if (StringUtil.isNotEmpty(cardIdcard)) {
	            sql += " and CARD_IDCARD_ = '" + cardIdcard + "'";
	        }
	        logger.info("queryCardInfos:" + sql);
	        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CardInfo.class));
	}
/*	public List<CardInfo> queryCardInfo(CardInfo paramsCard){
		StringBuffer buffer = new StringBuffer("SELECT * FROM card_info WHERE CARD_NO_ = ? ");
		List<Object> list = new ArrayList<>();
		list.add(paramsCard.getCARD_NO_());
		//新增流水号和商户号字段
		if(!StringUtil.isEmpty(paramsCard.getSERIALNO_())){
			buffer.append(" AND SERIALNO_ = ?");
			list.add(paramsCard.getSERIALNO_());
		}
		if(!StringUtil.isEmpty(paramsCard.getPARTNERNO_())){
			buffer.append(" AND PARTNERNO_ = ?");
			list.add(paramsCard.getPARTNERNO_());
		}
		if(!StringUtil.isEmpty(paramsCard.getCARD_NAME_())){
			buffer.append(" AND CARD_NAME_ = ?");
			list.add(paramsCard.getCARD_NAME_());
		}
		if(!StringUtil.isEmpty(paramsCard.getCARD_MOBILE_())){
			buffer.append(" AND CARD_MOBILE_ = ?");
			list.add(paramsCard.getCARD_MOBILE_());
		}
		if(!StringUtil.isEmpty(paramsCard.getCARD_IDCARD_())){
			buffer.append(" AND CARD_IDCARD_ = ?");
			list.add(paramsCard.getCARD_IDCARD_());
		}
		
		logger.info("----------------queryCardInfo:"+buffer.toString());
		logger.info("queryCardInfo:"+list.toString());
		List<CardInfo> cardInfos = jdbcTemplate.query(buffer.toString(),list.toArray(), new BeanPropertyRowMapper<CardInfo>(CardInfo.class));
		return cardInfos;
	}*/

	/** 
	* @Title: delCardInfo 
	* @Description: TODO
	* @param @param cardNo
	* @param @return
	* @return int
	* @throws 
	*/
	public int delCardInfo(String cardId) {
		return jdbcTemplate.update("delete from card_info where ID_ = '"+cardId+"'");
	}
}
